package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Register;
import com.wanmait.hotelManage.vo.Room;
import com.wanmait.hotelManage.vo.RoomType;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Register;
import com.wanmait.hotelManage.vo.Room;


public class RegisterDAO {
	private RegisterDAO() {};
	private static RegisterDAO registerdao=new RegisterDAO();
	public static RegisterDAO getRegisterdao() {
		return registerdao;
	}

	
	//查询所有的正在入驻(要退房)的人
	public List<Register> findAllCheckinRegister(Pager pager) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Register> registers=new ArrayList<Register>();
		Register register=null;
		
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT * FROM register,room,room_type ");
		sb.append(" WHERE room.check_in=1 AND room.id=register.room_id ");
		sb.append(" and room.room_type_id=room_type.id ");
		sb.append(" AND register.enable=1 and room.check_in=1 ");
		sb.append(" group by room_id");
		sb.append(" order by room.number limit ?,? ");
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				RoomType roomType=new RoomType();
				roomType.setId(rs.getInt("room_type.id"));
				roomType.setTitle(rs.getString("title"));
				
				Room room=new Room();
				room.setId(rs.getInt("room.id"));
				room.setCheckIn(true);
				room.setNumber(rs.getString("room.number"));
				room.setRoomtype(roomType);
				
				register=new Register();
				register.setBreakfast(rs.getBoolean("breakfast"));
				register.setDeposit(rs.getDouble("deposit"));
				register.setGender(rs.getBoolean("gender"));
				register.setId(rs.getInt("register.id"));
				register.setIdCard(rs.getString("id_card"));
				register.setName(rs.getString("name"));
				register.setStartTime(rs.getTimestamp("start_time"));
				register.setTelephoneNumber(rs.getString("telephone_number"));
				register.setRoom(room);
				
				registers.add(register);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return registers;
	}
	//获得正在入驻的总行数
	public int getCheckinRows() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) c from room where check_in=1";
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			return rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	//退房,删除入住信息
	public void delete(int roomId) {
		// TODO Auto-generated method stub
		String sql="update register set enable=0 where room_id=?";
		DBHelper.getDBHelper().executeUpdate(sql, roomId);
	}
	//根据房间号查找register 卢毅
	public Register findRegisterByRoomId(int roomId) {
		// TODO Auto-generated method stub
		Register register=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT * FROM register,room,room_type ");
		sb.append(" WHERE room.check_in=1 AND room.id=register.room_id ");
		sb.append(" and room.room_type_id=room_type.id ");
		sb.append(" AND register.enable=1 and room_type.enable=1 ");
		sb.append(" and room.number=? ");
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, roomId);
			rs=ps.executeQuery();
			if(rs.next()) {
				RoomType roomType=new RoomType();
				roomType.setId(rs.getInt("room_type.id"));
				roomType.setTitle(rs.getString("title"));
				
				Room room=new Room();
				room.setId(rs.getInt("room.id"));
				room.setCheckIn(true);
				room.setNumber(rs.getString("room.number"));
				room.setRoomtype(roomType);
				
				register=new Register();
				register.setBreakfast(rs.getBoolean("breakfast"));
				register.setDeposit(rs.getDouble("deposit"));
				register.setGender(rs.getBoolean("gender"));
				register.setId(rs.getInt("register.id"));
				register.setIdCard(rs.getString("id_card"));
				register.setName(rs.getString("name"));
				register.setStartTime(rs.getTimestamp("start_time"));
				register.setTelephoneNumber(rs.getString("telephone_number"));
				register.setRoom(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return register;
	}
	

	/**
	 * 添加register数据
	 * @param register
	 */
	public void insert(Register register) {
		String sql="insert into register(name,telephone_number,id_card,gender,breakfast,deposit,room_id,end_time)"
				+ " values(?,?,?,?,?,?,?,?)";
		Object[]params= {
				register.getName(),
				register.getTelephoneNumber(),
				register.getIdCard(),
				register.getGender(),
				register.getBreakfast(),
				register.getDeposit(),
				register.getRoom().getId(),
				register.getEndTime()
		};
		DBHelper.getDBHelper().executeUpdate(sql, params);
		
	}
	/**
	 * 根据名字电话号码查找所有
	 * @param findName
	 * @param findTelephone
	 * @return
	 */
//	public Register SearchByNameAndTelephone(String findName, String findTelephone) {
//		Register register=null;
//		Connection conn=null;
//		PreparedStatement ps=null;
//		ResultSet rs=null;
//		String sql="SELECT * FROM register,room WHERE NAME=? AND register.telephone_number=? AND register.room_id=room.id";
//		conn=DBHelper.getDBHelper().getConn();
//		try {
//			ps=conn.prepareStatement(sql);
//			ps.setString(1, findName);
//			ps.setString(2, findTelephone);
//			rs=ps.executeQuery();
//			Room room=null;
//			if(rs.next()){
//				register=new Register();
//				register.setName(rs.getString("name"));
//				register.setTelephoneNumber(rs.getString("telephone_number"));
//				register.setIdCard(rs.getString("id_card"));
//				register.setStartTime(rs.getTimestamp("start_time"));
//				register.setEndTime(rs.getTimestamp("end_time"));
//				register.setGender(rs.getBoolean("gender"));
//				register.setBreakfast(rs.getBoolean("breakfast"));
//				room=new Room();
//				room.setId(rs.getInt("room_id"));
//				room.setNumber(rs.getString("number"));
//				register.setRoom(room);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally{
//			DBHelper.getDBHelper().close(rs, ps, conn);
//		}
//		return register;
//	}
//	public static void main(String[] args) {
//		Register r=RegisterDAO.getRegisterdao().SearchByNameAndTelephone("蓝晓", "17811111111");
//		System.out.println(r.getRoom().getNumber());
//	}
	/**
	 * 判断是否相同
	 * @param register
	 * @return
	 */
	public Boolean booleanRegister(Register register) {
		boolean flag=false;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from register,room where name=? "
				+ " and telephone_number=? and id_card=? and gender=? "
				+ " and breakfast=? and deposit=? and room_id=? and end_time=? "
				+ " and room_id=room.id and room.number=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, register.getName());
			ps.setString(2, register.getTelephoneNumber());
			ps.setString(3, register.getIdCard());
			ps.setBoolean(4, register.getGender());
			ps.setBoolean(5, register.getBreakfast());
			ps.setDouble(6, register.getDeposit());
			ps.setInt(7, register.getRoom().getId());
			ps.setTimestamp(8, register.getEndTime());
			ps.setString(9, register.getRoom().getNumber());
			rs=ps.executeQuery();
			if(rs.next()) {
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return flag;
	}
	/**
	 * 按照姓名电话进行更新
	 * @param register
	 */
//	public void update(Register register) {
//		String sql="update register set end_time=?,breakfast=?,room_id=? where name=? and telephone_number=?";
//		Object[]params= {
//				register.getEndTime(),
//				register.getBreakfast(),
//				register.getRoom().getId(),
//				register.getName(),
//				register.getTelephoneNumber()
//		};
//		DBHelper.getDBHelper().executeUpdate(sql, params);
//	}

	//查找所有客史
	public List<Register> findRoomHistoryRegister(Pager pager) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Register> registers=new ArrayList<Register>();
		StringBuilder sb=new StringBuilder();
		sb.append("SELECT * FROM register,room,room_type ");
		sb.append("WHERE register.room_id=room.id ");
		sb.append("AND room.room_type_id=room_type.id ");
		sb.append("AND register.`enable`=0 ");
		sb.append("order by start_time desc ");
		sb.append("limit ?,?");	
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sb.toString());	
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()) {
				RoomType roomType=new RoomType();
				roomType.setTitle(rs.getString("room_type.title"));
				Room room=new Room();
				room.setRoomtype(roomType);
				room.setNumber(rs.getString("room.number"));
				Register register =new Register();
				register.setRoom(room);
				register.setBreakfast(rs.getBoolean("register.breakfast"));
				register.setGender(rs.getBoolean("gender"));
				register.setId(rs.getInt("register.id"));
				register.setEndTime(rs.getTimestamp("end_time"));
				register.setDeposit(rs.getDouble("deposit"));
				register.setIdCard(rs.getString("id_card"));
				register.setName(rs.getString("register.name"));
				register.setRemark(rs.getString("register.remark"));
				register.setStartTime(rs.getTimestamp("register.start_time"));
				register.setTelephoneNumber(rs.getString("telephone_number"));
				
				registers.add(register);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return registers;
	}

	//获得客史记录数
	public int getCheckOutRows() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		String sql="select count(*) c from register where enable =0";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			return rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return 0;
	}

	//按照条件搜索register
	public List<Register> searchRegisters(Register register, Pager pager) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Register> registers=new ArrayList<Register>();
		
		StringBuilder sb=new StringBuilder();
		sb.append("select * from  register,room,room_type ");
		sb.append("WHERE register.room_id=room.id ");
		sb.append("AND room.room_type_id=room_type.id ");
		sb.append("AND register.`enable`=0 ");
		if(register.getIdCard()!="") {
			sb.append(" and register.id_card= '"+register.getIdCard()+"'");
		}
		if(register.getRoom().getNumber()!="") {
			sb.append(" and room.number= '"+register.getRoom().getNumber()+"'");
		}
		if(register.getStartTime()!=null) {
			sb.append(" and datediff(start_time,'"+register.getStartTime()+"')=0");
		}
		if(register.getEndTime()!=null) {
			sb.append(" and datediff(end_time,'"+register.getEndTime()+"')=0");
		}
		sb.append(" order by start_time desc");
		sb.append(" limit "+pager.getStart()+","+pager.getPageSize());
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			while(rs.next()) {
				RoomType roomType=new RoomType();
				roomType.setTitle(rs.getString("room_type.title"));
				Room room=new Room();
				room.setRoomtype(roomType);
				room.setNumber(rs.getString("room.number"));
				Register register1 =new Register();
				register1.setRoom(room);
				register1.setBreakfast(rs.getBoolean("register.breakfast"));
				register1.setGender(rs.getBoolean("gender"));
				register1.setId(rs.getInt("register.id"));
				register1.setEndTime(rs.getTimestamp("end_time"));
				register1.setDeposit(rs.getDouble("deposit"));
				register1.setIdCard(rs.getString("id_card"));
				register1.setName(rs.getString("register.name"));
				register1.setRemark(rs.getString("register.remark"));
				register1.setStartTime(rs.getTimestamp("register.start_time"));
				register1.setTelephoneNumber(rs.getString("telephone_number"));
				
				registers.add(register1);
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		
		return registers;
	}

	//获得符合搜索条件的函数
	public int getHistoryRows(Register register) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		StringBuilder sb=new StringBuilder();
		sb.append("select count(*) c from register,room ");
		sb.append("WHERE register.room_id=room.id ");
		sb.append("AND register.`enable`=0 ");
		if(register.getIdCard()!="") {
			sb.append(" and register.id_card= '"+register.getIdCard()+"'");
		}
		if(register.getRoom().getNumber()!="") {
			sb.append("and room.number= '"+register.getRoom().getNumber()+"'");
		}
		if(register.getStartTime()!=null) {
			sb.append(" and datediff(start_time,'"+register.getStartTime()+"')=0");
		}
		if(register.getEndTime()!=null) {
			sb.append(" and datediff(end_time,'"+register.getEndTime()+"')=0");
		}
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			return rs.getInt("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return 0;
	}


	public void updateEndTime(String roomId) {
		// TODO Auto-generated method stub
		String sql="update register set end_time=now() where room_id=?";
		DBHelper.getDBHelper().executeUpdate(sql,Integer.parseInt(roomId));
	}

	/**
	 * room表里面查找人物信息
	 * @param id
	 */
	public List<Register> findRoomId(Integer id,Pager pager) {
		List<Register>registers=new ArrayList<Register>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM register WHERE register.room_id=? limit ?,?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setInt(2, pager.getStart());
			ps.setInt(3, pager.getPageSize());
			rs=ps.executeQuery();
			Register register=null;
			while(rs.next()) {
				register=new Register();
				register.setId(rs.getInt("id"));
				register.setName(rs.getString("name"));
				register.setTelephoneNumber(rs.getString("telephone_number"));
				register.setIdCard(rs.getString("id_card"));
				register.setGender(rs.getBoolean("gender"));
				register.setBreakfast(rs.getBoolean("breakfast"));
				register.setDeposit(rs.getDouble("deposit"));
				register.setStartTime(rs.getTimestamp("start_time"));
				register.setEndTime(rs.getTimestamp("end_time"));
				registers.add(register);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return registers;
	}
//	public static void main(String[] args) {
//		int rows=RegisterDAO.getRegisterdao().getRoomRows(1);
//		System.out.println(rows);
//	}

	/**
	 * 获取room人的信息的行数
	 * @param id
	 * @return
	 */
	public int getRoomRows(int id) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT count(*) c FROM register WHERE register.room_id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				int rows=rs.getInt("c");
				return rows;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
}
